﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using System.Xml;

namespace dbschemaupdater
{
    static class Model
    {
        public class Table
        {
            public DAL.TABLE table { get; set; }
            public DAL.COLUMN[] columns { get; set; }
        }


        public static XDocument Generate(string connectionString)
        {
            using (var db = new DAL(connectionString))
            {
                var columns = db.COLUMNS.ToArray();

                var xdoc = new XDocument();
                var xroot = new XElement("schema");
                var xtables = new XElement("tables");
                xtables.Add(
                    from r in db.TABLES
                    select new XElement("TABLE",
                        new XAttribute("NAME", r.NAME),
                        new XAttribute("SCHEMA", r.SCHEMA),
                        new XElement("columns",
                            from c in columns
                            where c.TABLE_SCHEMA == r.SCHEMA && c.TABLE_NAME == r.NAME
                            select new XElement("COLUMN",
                                new XAttribute("COLUMN_NAME", c.COLUMN_NAME),
                                new XAttribute("ORDINAL_POSITION", c.ORDINAL_POSITION),
                                new XAttribute("DATA_TYPE", c.DATA_TYPE),
                                new XAttribute("IS_NULLABLE", c.IS_NULLABLE),
                                c.CHARACTER_MAXIMUM_LENGTH.HasValue ? new XAttribute("CHARACTER_MAXIMUM_LENGTH", c.CHARACTER_MAXIMUM_LENGTH) : null,
                                c.NUMERIC_PRECISION.HasValue ? new XAttribute("NUMERIC_PRECISION", c.NUMERIC_PRECISION) : null,
                                c.NUMERIC_SCALE.HasValue ? new XAttribute("NUMERIC_SCALE", c.NUMERIC_SCALE) : null,
                                c.DATETIME_PRECISION.HasValue ? new XAttribute("DATETIME_PRECISION", c.DATETIME_PRECISION) : null,
                                c.COLLATION_NAME != null ? new XAttribute("COLLATION_NAME", c.COLLATION_NAME) : null,
                                new XAttribute("IsIdentity", c.IsIdentity),
                                c.IsIdentity != 0 && c.IdentSeed.HasValue ? new XAttribute("IdentSeed", c.IdentSeed) : null,
                                c.IsIdentity != 0 && c.IdentIncr.HasValue ? new XAttribute("IdentIncr", c.IdentIncr) : null,
                                c.PrimaryKey.HasValue ? new XAttribute("PrimaryKey", c.PrimaryKey) : null
                            )
                        )
                    )
                );
                xroot.Add(xtables);
                xdoc.Add(xroot);
                return xdoc;
            }
        }

        public static Table[] Load(string connectionString)
        {
            using (var db = new DAL(connectionString))
            {
                var columns = db.COLUMNS.ToArray();
                return db.TABLES.Select(t => new Table
                {
                    table = t,
                    columns = columns.Where(c =>
                        string.Equals(c.TABLE_NAME, t.NAME, StringComparison.InvariantCultureIgnoreCase) && string.Equals(c.TABLE_SCHEMA, t.SCHEMA, StringComparison.InvariantCultureIgnoreCase)
                        ).OrderBy(c => c.ORDINAL_POSITION).ToArray()
                }).ToArray();
            }
        }

        public static Table[] Load(System.IO.TextReader reader)
        {
            XDocument xdoc = XDocument.Load(reader, LoadOptions.None);

            return xdoc.Root.Element("tables").Elements("TABLE").Where(xtbl => xtbl.Attribute("NAME") != null && xtbl.Attribute("SCHEMA") != null).Select(xtbl =>
                new Table
                {
                    table = new DAL.TABLE
                    {
                        NAME = AttributeValue(xtbl.Attribute("NAME")),
                        SCHEMA = AttributeValue(xtbl.Attribute("SCHEMA"))
                    },
                    columns = xtbl.Element("columns").Elements("COLUMN").Select(xcol =>
                        new DAL.COLUMN
                        {
                            TABLE_NAME = AttributeValue(xtbl.Attribute("NAME")),
                            TABLE_SCHEMA = AttributeValue(xtbl.Attribute("SCHEMA")),
                            COLUMN_NAME = AttributeValue(xcol.Attribute("COLUMN_NAME")),
                            DATA_TYPE = AttributeValue(xcol.Attribute("DATA_TYPE")),
                            COLLATION_NAME = AttributeValue(xcol.Attribute("COLLATION_NAME")),
                            IS_NULLABLE = AttributeValue(xcol.Attribute("IS_NULLABLE")),
                            ORDINAL_POSITION = AttributeInt(xcol.Attribute("ORDINAL_POSITION")) ?? 0,
                            CHARACTER_MAXIMUM_LENGTH = AttributeInt(xcol.Attribute("CHARACTER_MAXIMUM_LENGTH")),
                            DATETIME_PRECISION = AttributeShort(xcol.Attribute("DATETIME_PRECISION")),
                            NUMERIC_PRECISION = AttributeByte(xcol.Attribute("NUMERIC_PRECISION")),
                            NUMERIC_SCALE = AttributeInt(xcol.Attribute("NUMERIC_SCALE")),
                            PrimaryKey = AttributeInt(xcol.Attribute("PrimaryKey")),
                            IsIdentity = AttributeInt(xcol.Attribute("IsIdentity")) ?? 0,
                            IdentIncr = AttributeLong(xcol.Attribute("IdentIncr")),
                            IdentSeed = AttributeLong(xcol.Attribute("IdentSeed"))
                        }).ToArray()
                }
            ).ToArray();
        }

        [System.Diagnostics.DebuggerStepThrough]
        private static string AttributeValue(XAttribute attr)
        {
            if (attr != null)
                return attr.Value;
            else
                return null;
        }

        [System.Diagnostics.DebuggerStepThrough]
        private static int? AttributeInt(XAttribute attr)
        {
            int value;
            if (attr != null && int.TryParse(attr.Value, out value))
                return value;
            else
                return null;
        }

        [System.Diagnostics.DebuggerStepThrough]
        private static short? AttributeShort(XAttribute attr)
        {
            short value;
            if (attr != null && short.TryParse(attr.Value, out value))
                return value;
            else
                return null;
        }

        [System.Diagnostics.DebuggerStepThrough]
        private static byte? AttributeByte(XAttribute attr)
        {
            byte value;
            if (attr != null && byte.TryParse(attr.Value, out value))
                return value;
            else
                return null;
        }

        [System.Diagnostics.DebuggerStepThrough]
        private static long? AttributeLong(XAttribute attr)
        {
            long value;
            if (attr != null && long.TryParse(attr.Value, out value))
                return value;
            else
                return null;
        }
    }
}
